from DatetimeUtils import choose_day
import pandas as pd
import time
from ListUtils import anti_join


"""
备用行情数据
"""

def deal_append(query_start_, query_end_, engine_, pro_):
    """
    通过anti_join函数处理增量数据
    """
    sql = 'select t.* from trade_cal t WHERE t.is_open = "1" and t.cal_date >= ' + query_start_ + ' and t.cal_date <= ' + query_end_
    df = pd.read_sql(sql, engine_)
    # df = df[df['is_open'] == '1']
    # df = df.reset_index(drop=True)
    # print('trade_cal：(%s)' % df)
    if len(df) > 0:
        for j in range(0, len(df)):
            # print('df.loc[j]：(%s)' % df.loc[j])
            trade_date = df.loc[j].cal_date
            # print('trade_date：(%s)' % trade_date)
            sql = 'select b.ts_code,b.trade_date from bak_daily b WHERE b.trade_date = ' + trade_date
            bak_daily_target = pd.read_sql(sql, engine_)
            bak_daily_source = pro_.bak_daily(trade_date=trade_date)
            if len(bak_daily_target) == 0:
                bak_daily_source.to_sql('bak_daily', con=engine_, index=False, index_label=['ts_code', 'trade_date'],
                                        if_exists='append')
                print('数据库内未找到数据则全部添加：(%s)' % bak_daily_source)
                time.sleep(2)
            else:
                bak_daily_source = anti_join(bak_daily_source, bak_daily_target, ['ts_code', 'trade_date'])
                print('增量：(%s)' % bak_daily_source)
                if len(bak_daily_source) > 0:
                    bak_daily_source.to_sql('bak_daily', con=engine_, index=False, index_label=['ts_code', 'trade_date'],
                                            if_exists='append')
                    time.sleep(2)



def insert_history_data_bak_daily(engine_, pro_):
    """
    查询并保存历史数据
    """
    insert_history_data_flag = True
    if insert_history_data_flag:
        current_year = choose_day(0).strftime('%Y')
        start_year = 2017  # 起始日为20170614
        """
        处理历史数据
        """
        print('-----------------处理历史数据------------------')
        for i in range(start_year, int(current_year)):
            query_start = '%d' % i + '0101'  # 查询开始
            if i == 2017:
                query_start = '20170614'  # 由于接口限制日查询500次，记录上次数据日期20190415
            query_end = '%d' % i + '1231'  # 查询结束
            deal_append(query_start, query_end, engine_, pro_)

        """
        处理当年数据
        """
        print('----------------处理当年数据------------------')
        current_day = choose_day(0).strftime('%Y%m%d')
        year_first_day = current_year + '0101'
        deal_append(year_first_day, current_day, engine_, pro_)



def update_current_data_bak_daily(engine_, pro_):
    """
    查询并更新数据，定时器使用
    """
    day_updates = 30  # 设定更新30天数据
    current_day = choose_day(0).strftime('%Y%m%d') # 备用数据比正常数据迟一天
    that_day = choose_day(day_updates).strftime('%Y%m%d')
    deal_append(that_day, current_day, engine_, pro_)